package com.AddressListModule.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import util.ConnectDB;
import util.UtilDAO;

import com.AddressListModule.bean.ContactBean;
import com.AddressListModule.bean.DirectoryBean;
import com.AddressListModule.bean.TbUserDirectory;

public class AddressModuleDao {

	/**
	 *@author qingyu zhang
	 *@function:
	 *2011-2-17
	 *下午02:13:22
	 *AddressListModule
	 *com.AddressListModule.dao
	 */
	// 数据库连接
	private Connection conn = null;
	// st对象
	private PreparedStatement pt = null;

	private Statement st = null;
	// 结果集
	private ResultSet rs = null;
	// 成功失败标志
	private boolean flag = false;
	// 要执行的语句
	private String sql = "";
	private DirectoryBean directoryBean = null;
	private ContactBean contactBean = null;
	private List<DirectoryBean> directoryList = null;
	private List<ContactBean> contactList = null;
	private List<TbUserDirectory> userDirectoryList = null;
	/**
	 * 
	 *@author qingyu zhang
	 *@function:add a record of directory
	 * @param direct
	 * @return
	 *2011-2-17
	 *下午02:17:47
	 *AddressListModule
	 *com.AddressListModule.dao
	 *boolean
	 */
	public boolean add(DirectoryBean direct){
		boolean bo = false;
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql = "insert into tb_directory (dirLevel,parentDirId,dirName,userId,createTime) " +
					"values("+direct.getDirLevel()+","+direct.getParentDirId()+",'"+direct.getDirName()+"',"+direct.getUserId()+",'"+direct.getCreateTime()+"')";
			pt = conn.prepareStatement(sql);
			bo = pt.execute();
			bo = true;
		}catch(Exception e){
			e.printStackTrace();
			bo = false;
		}finally{
			ConnectDB.closeSqlConnection();
		}
		return bo;
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:add a contact
	 * @param contactBean
	 * @return
	 *2011-2-22
	 *下午02:18:17
	 *AddressListModule
	 *com.AddressListModule.dao
	 *boolean
	 */
	public boolean addContact(ContactBean contactBean){
		boolean bo = false;
		try
		{
			conn = ConnectDB.getSqlServerConnection();
			sql = "insert into tb_customer (customerName,customerPhone,lastTime,dirId,customerIntegral) " +
					"values('"+contactBean.getCustomerName()+"','"+contactBean.getCustomerPhone()+"','"+contactBean.getLastTime()
					+"',"+contactBean.getDirId()+",'"+contactBean.getCustomerIntegral()+"')";
			System.out.println("新增SQL＝＝"+sql);
			pt = conn.prepareStatement(sql);
			bo = pt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
		}
		return bo;
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:save directory with arrayList
	 * @param directList
	 * @return
	 *2011-2-17
	 *下午02:18:47
	 *AddressListModule
	 *com.AddressListModule.dao
	 *boolean
	 */
	public boolean addList(ArrayList directList){
		return true;
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get List of directory by condition
	 * @param tableName
	 * @param order
	 * @param condition
	 * @return
	 *2011-2-17
	 *下午02:20:20
	 *AddressListModule
	 *com.AddressListModule.dao
	 *List<DirectoryBean>
	 */
	public List<DirectoryBean> getAllTbDirectory(String tableName,String order,String condition){
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql= UtilDAO.getList(tableName, order, condition);
			System.out.println("查询语句"+sql);
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){
				if(directoryList==null){
					directoryList = new ArrayList<DirectoryBean>();
				}
				directoryList.add(this.getDirectory(rs));
			}
			
		}catch(Exception e){
			System.out.println("error!!!!!!!!!!");
			return null;
		}finally{
			ConnectDB.closeSqlConnection();
		}
		return directoryList;
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get a object from ResultSet
	 * @param rs
	 * @return
	 *2011-2-17
	 *下午03:03:01
	 *AddressListModule
	 *com.AddressListModule.dao
	 *DirectoryBean
	 */
	public DirectoryBean getDirectory(ResultSet rs){
		DirectoryBean directory = new DirectoryBean();
		try{
			directory.setCreateTime(rs.getString("createTime"));
			directory.setDirId(rs.getInt("dirId"));
			directory.setDirLevel(rs.getInt("dirLevel"));
			directory.setDirName(rs.getString("dirName"));
			directory.setParentDirId(rs.getInt("parentDirId"));
			directory.setUserId(rs.getInt("userId"));
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			return directory;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get the linkMan by condition
	 * @param tableName
	 * @param order
	 * @param condition
	 * @return
	 *2011-2-19
	 *下午02:53:57
	 *AddressListModule
	 *com.AddressListModule.dao
	 *List<ContactBean>
	 */
	public List<ContactBean> getContactList(String tableName,String order,String condition){
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql= UtilDAO.getListT(tableName, order, condition);
			pt = conn.prepareStatement(sql);
			System.out.println("sql==="+sql);
			rs = pt.executeQuery();
			while(rs.next()){
				if(contactList==null){
					contactList = new ArrayList();
				}
				contactList.add(this.getContact(rs));
			}
			
		}catch(Exception e){
			System.out.println("error!!!!!!!!!!");
			return null;
		}finally{
			ConnectDB.closeSqlConnection();
		}
		return contactList;
		
		
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get a object of linkMan
	 * @param rs
	 * @return
	 *2011-2-19
	 *下午02:54:52
	 *AddressListModule
	 *com.AddressListModule.dao
	 *ContactBean
	 */
	public ContactBean getContact(ResultSet rs){
		ContactBean contactBean = new ContactBean();
		try{
			contactBean.setCustomerId(rs.getInt("customerId"));
			contactBean.setCustomerIntegral(rs.getString("customerIntegral"));
			contactBean.setCustomerName(rs.getString("customerName"));;
			contactBean.setCustomerPhone(rs.getString("customerPhone"));
			contactBean.setDirId(rs.getInt("dirId"));
			contactBean.setLastTime(rs.getString("lastTime"));
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			return contactBean;
		}	
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get the ID of Directory by MAX(ID)
	 * @param tableName
	 * @param condition
	 * @return
	 *2011-2-22
	 *上午09:44:29
	 *AddressListModule
	 *com.AddressListModule.dao
	 *DirectoryBean
	 */
	public int getDirectory(String tableName){
		int maxDirId = 0;
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql = "select max(dirId) maxDirId from "+tableName;
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			if(rs.next()){
				maxDirId = rs.getInt("maxDirId");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
			return maxDirId;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get a directory by dirId
	 * @param tablName
	 * @param inKey
	 * @param dirId
	 * @return
	 *2011-2-22
	 *上午10:12:32
	 *AddressListModule
	 *com.AddressListModule.dao
	 *DirectoryBean
	 */
	public DirectoryBean getDirectory(String tablName,String inKey,int dirId){
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql = UtilDAO.getObject(tablName, inKey, dirId);
			pt =conn.prepareStatement(sql);
			rs = pt.executeQuery();
			if(rs.next()){
				if(directoryBean==null){
					directoryBean = new DirectoryBean();
				}
				directoryBean = this.getDirectory(rs);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
			return directoryBean;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:modify a Directory
	 * @param directoryBean
	 * @return
	 *2011-2-22
	 *上午10:27:18
	 *AddressListModule
	 *com.AddressListModule.dao
	 *boolean
	 */
	public boolean modifyDirectory(DirectoryBean directoryBean){
		boolean bo = false;
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql = "update tb_directory set dirName='"+directoryBean.getDirName()+"' where dirId="+directoryBean.getDirId();
			pt = conn.prepareStatement(sql);
			bo = pt.execute();
		}catch(Exception e){
			e.printStackTrace();
			bo = false;
		}finally{
			ConnectDB.closeSqlConnection();
			return bo;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:update a linkMan
	 * @param contactBean
	 * @return
	 *2011-2-23
	 *下午01:37:06
	 *AddressListModule
	 *com.AddressListModule.dao
	 *boolean
	 */
	public boolean modifyContact(ContactBean contactBean){
		boolean bo = false;
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql = "update tb_customer set customerName='"+contactBean.getCustomerName()+"',customerPhone='"+contactBean.getCustomerPhone()
			+"',lastTime='"+contactBean.getLastTime()+"',customerIntegral='"+contactBean.getCustomerIntegral()+"' where customerId="+contactBean.getCustomerId();
			System.out.println("修改的语句"+sql);
			pt = conn.prepareStatement(sql);
			bo = pt.execute();
		}catch(Exception e){
			e.printStackTrace();
			
		}finally{
			ConnectDB.closeSqlConnection();
			return bo;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get the chilDirIdList of parDirId
	 * @param parDirId
	 * @return
	 *2011-2-24
	 *下午03:03:27
	 *AddressListModule
	 *com.AddressListModule.dao
	 *String
	 */
	public String getChilDirId(String tableName,String order,String condition){
		String chilDirId = "";
		try{
			sql= UtilDAO.getList(tableName, order, condition); 
			conn = ConnectDB.getSqlServerConnection();
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){
				chilDirId+=this.getDirectory(rs).getDirId()+",";
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
			return chilDirId;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get a ContactBean by ID
	 * @param contactId
	 * @return
	 *2011-2-23
	 *下午03:56:09
	 *AddressListModule
	 *com.AddressListModule.dao
	 *ContactBean
	 */
	public ContactBean getContact(String contactId){
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql = "select * from tb_customer where customerId="+contactId;
			pt =conn.prepareStatement(sql);
			rs = pt.executeQuery();
			if(rs.next()){
				if(contactBean==null){
					contactBean = new ContactBean();
				}
				contactBean = this.getContact(rs);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
			return contactBean;
		}
	}
	
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get the directory from tb_userDirectory
	 * @param tableName
	 * @param orde
	 * @param condition
	 * @return
	 *2011-2-25
	 *上午10:28:07
	 *MmsModule
	 *com.AddressListModule.dao
	 *List<TbUserDirectory>
	 */
	public List<TbUserDirectory> getUserDirByRole(String tableName,String order ,String condition){
		try{
			conn = ConnectDB.getSqlServerConnection();
			sql= UtilDAO.getList(tableName, order, condition);
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){
				if(userDirectoryList==null){
					userDirectoryList = new ArrayList();
				}
				userDirectoryList.add(this.getUserDirectory(rs));
			}
			
		}catch(Exception e){
			System.out.println("error!!!!!!!!!!");
			return null;
		}finally{
			ConnectDB.closeSqlConnection();
			return userDirectoryList;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get a object of TbUserDirectory
	 * @param rs
	 * @return
	 *2011-2-25
	 *上午10:30:24
	 *MmsModule
	 *com.AddressListModule.dao
	 *TbUserDirectory
	 */
	public TbUserDirectory getUserDirectory(ResultSet rs){
		TbUserDirectory tbUserDir = new TbUserDirectory();
		try
		{
			tbUserDir.setCreateDate(rs.getString("createDate"));
			tbUserDir.setDirectoryId(rs.getInt("directoryId"));
			tbUserDir.setUser_directoryId(rs.getInt("user_directoryId"));
			tbUserDir.setUserId(rs.getInt("userId"));
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
			return tbUserDir;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:save contact by List
	 * @param contactList
	 * @return
	 *2011-3-7
	 *下午08:43:16
	 *bzsafety
	 *com.AddressListModule.dao
	 *boolean
	 */
	public boolean saveContactList(List<ContactBean> contactList){
		boolean bo = false;
		try
		{
			conn = ConnectDB.getSqlServerConnection();
			st = conn.createStatement();
			if(contactList!=null&&contactList.size()>0){
				for(ContactBean contact:contactList){
					String hql = "insert into tb_customer (customerName,customerPhone,lastTime,dirId,customerIntegral) " +
					"values('"+contact.getCustomerName()+"','"+contact.getCustomerPhone()+"','"+contact.getLastTime()
					+"',"+contact.getDirId()+",'"+contact.getCustomerIntegral()+"')";
					System.out.println("hql==="+hql);
					st.addBatch(hql);
				}
				st.executeBatch();
				bo = true;
			}
		}catch(Exception e){
			e.printStackTrace();
			bo = false;
		}finally{
			ConnectDB.closeSqlConnection();
			return bo ;
		}
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get a directory by dirId
	 * @param dirId
	 * @return
	 *2011-3-18
	 *上午11:32:43
	 *bzsafety
	 *com.AddressListModule.dao
	 *DirectoryBean
	 */
	public DirectoryBean getDirect(int dirId){
		
		try {
			conn = ConnectDB.getSqlServerConnection();
			sql="select * from tb_directory where dirId="+dirId;
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
				while(rs.next()){
					if(directoryBean==null){
						directoryBean = new DirectoryBean();
						directoryBean = getDirectory(rs);
					}
				}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
			return directoryBean;
		}
	}
	public int getMaxContantId(){
		int maxNum = 0;
		try {
			conn = ConnectDB.getSqlServerConnection();
			sql = "select Max(customerId) maxId from tb_customer";
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			if(rs.next()){
				maxNum = rs.getInt("maxId");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
			return maxNum;
		}
	}
}
